package com.taocore.top.title.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;

import javax.naming.NamingException;

import com.taobao.api.domain.ItemCat;
import com.taocore.top.title.domain.RealTimeKeyword;
import com.taocore.top.title.domain.TitleHistoryItem;
import com.taocore.top.title.domain.WeeklyKeyword;

public class Dao {
	
	static Logger _logger = Logger.getLogger(Dao.class.getName());
	
	public static final Dao INSTANCE = new Dao();
	
	private Dao()
	{
		
	}
	
	public synchronized void updateUser(long uid, String nick, String session, short version) throws NamingException, SQLException
	{
		Connection conn = null;
		PreparedStatement statement = null;
		try
		{
			conn = DBUtils.getConnection();
			String sql = "update user_t set session_c=?, version_c=?, last_login_c=? where user_id_c=?";
			statement = conn.prepareStatement(sql);
			statement.setString(1, session);
			statement.setShort(2, version);
			statement.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
			statement.setLong(4, uid);
			int result = statement.executeUpdate();
			if (result == 0)
			{
				sql = "insert into user_t(user_id_c, nick_c, session_c, version_c, last_login_c) values(?,?,?,?,?)";
				statement = conn.prepareStatement(sql);
				statement.setLong(1, uid);
				statement.setString(2, nick);
				statement.setString(3, session);
				statement.setShort(4, version);
				statement.setTimestamp(5, new Timestamp(System.currentTimeMillis()));
			}
			statement.executeUpdate();
		}
		finally
		{
			DBUtils.close(conn, statement, null);
		}
	}
	
	public String getTopSession(long userId) throws NamingException, SQLException
	{
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try
		{
			conn = DBUtils.getConnection();
			String sql = "select session_c from user_t where user_id_c=?";
			statement = conn.prepareStatement(sql);
			statement.setLong(1, userId);
			rs = statement.executeQuery();
			if (rs.next())
			{
				return rs.getString("session_c");
			}
		}
		finally
		{
			DBUtils.close(conn, statement, rs);
		}
		return null;
	}
	
	public String getTopSession(String nick) throws NamingException, SQLException
	{
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try
		{
			conn = DBUtils.getConnection();
			String sql = "select session_c from user_t where nick_c=?";
			statement = conn.prepareStatement(sql);
			statement.setString(1, nick);
			rs = statement.executeQuery();
			if (rs.next())
			{
				return rs.getString("session_c");
			}
		}
		finally
		{
			DBUtils.close(conn, statement, rs);
		}
		return null;
	}
	
	public short getVersion(long userId) throws NamingException, SQLException
	{
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try
		{
			conn = DBUtils.getConnection();
			String sql = "select version_c from user_t where user_id_c=?";
			statement = conn.prepareStatement(sql);
			statement.setLong(1, userId);
			rs = statement.executeQuery();
			if (rs.next())
			{
				return rs.getShort("version_c");
			}
		}
		finally
		{
			DBUtils.close(conn, statement, rs);
		}
		return -1;
	}
	
	public void clearWeeklyKeywords(Connection conn) throws SQLException
	{
		Statement statement = null;
		try
		{
			String sql = "delete from week_keywords_t";
			statement = conn.createStatement();
			statement.execute(sql);
		}
		finally
		{
			DBUtils.close(null, statement, null);
		}
	}
	
	public List<WeeklyKeyword> getWeeklyKeywordsByCoreWord(String coreWord, int limit, int offset, Connection conn) throws NamingException, SQLException
	{
		PreparedStatement statement = null;
		ResultSet rs = null;
		try
		{
			List<WeeklyKeyword> keywords = new ArrayList<WeeklyKeyword>();
			String sql = "select keyword_c, clicks_c from week_keywords_t where keyword_c like ? order by clicks_c desc limit ? offset ?";
			statement = conn.prepareStatement(sql);
			statement.setString(1, "%" + coreWord + "%");
			statement.setInt(2, limit);
			statement.setInt(3, offset);
			rs = statement.executeQuery();
			while (rs.next())
			{
				WeeklyKeyword keyword = new WeeklyKeyword();
				keyword.setKeyword(rs.getString("keyword_c"));
				keyword.setClicks(rs.getLong("clicks_c"));
				keywords.add(keyword);
			}
			return keywords;
		}
		finally
		{
			DBUtils.close(null, statement, rs);
		}
	}
	
	public List<WeeklyKeyword> getWeeklyKeywordsByCategories(String[] cats, int limit, int offset, Connection conn) throws NamingException, SQLException
	{
		if (cats == null || cats.length != 3)
		{
			return null;
		}
		PreparedStatement statement = null;
		ResultSet rs = null;
		try
		{
			List<WeeklyKeyword> keywords = new ArrayList<WeeklyKeyword>();
			String sql = "select keyword_c, clicks_c from week_keywords_t where c1_name_c=? and c2_name_c=? and c3_name_c=? order by clicks_c desc limit ? offset ?";
			statement = conn.prepareStatement(sql);
			for (int i = 1; i <= 3; i++)
			{
				String cat = cats[i - 1];
				_logger.info("cat: " + cat);
				if (cat == null)
				{
					cat = "";
				}
				statement.setString(i, cat);
			}
			statement.setInt(4, limit);
			statement.setInt(5, offset);
			rs = statement.executeQuery();
			while (rs.next())
			{
				WeeklyKeyword keyword = new WeeklyKeyword();
				keyword.setKeyword(rs.getString("keyword_c"));
				keyword.setClicks(rs.getLong("clicks_c"));
				keywords.add(keyword);
			}
			return keywords;
		}
		finally
		{
			DBUtils.close(null, statement, rs);
		}
	}
	
	public void clearRealTimeKeywords(Connection conn) throws SQLException
	{
		Statement statement = null;
		try
		{
			String sql = "delete from day_keywords_t";
			statement = conn.createStatement();
			statement.execute(sql);
		}
		finally
		{
			DBUtils.close(null, statement, null);
		}
	}
	
	public List<RealTimeKeyword> getRealTimeKeywordsByCoreWord(String coreWord, int limit, int offset, Connection conn) throws NamingException, SQLException
	{
		PreparedStatement statement = null;
		ResultSet rs = null;
		try
		{
			List<RealTimeKeyword> keywords = new ArrayList<RealTimeKeyword>();
			String sql = "select keyword_c from day_keywords_t where keyword_c like ? order by idx_c desc limit ? offset ?";
			statement = conn.prepareStatement(sql);
			statement.setString(1, "%" + coreWord + "%");
			statement.setInt(2, limit);
			statement.setInt(3, offset);
			rs = statement.executeQuery();
			while (rs.next())
			{
				RealTimeKeyword keyword = new RealTimeKeyword();
				keyword.setKeyword(rs.getString("keyword_c"));
				keywords.add(keyword);
			}
			return keywords;
		}
		finally
		{
			DBUtils.close(null, statement, rs);
		}
	}
	
	public void storeCategories(List<ItemCat> cats, int level, Connection conn) throws SQLException
	{
		if (cats == null)
		{
			return;
		}
		PreparedStatement statement = null;
		try
		{
			StringBuilder sb = new StringBuilder("insert into ");
			sb.append("cat").append(level).append("_t")
			.append("(cid_c, name_c, parent_cid_c) values(?,?,?)");
			statement = conn.prepareStatement(sb.toString());
			for (ItemCat cat : cats)
			{
				statement.setLong(1, cat.getCid());
				statement.setString(2, cat.getName());
				statement.setLong(3, cat.getParentCid());
				statement.addBatch();
			}
			statement.executeBatch();
		}
		finally
		{
			DBUtils.close(null, statement, null);
		}
	}
	
	public void clearCategories(Connection conn) throws SQLException
	{
		Statement statement = null;
		try
		{
			String sql = "delete from cat3_t";
			statement = conn.createStatement();
			statement.execute(sql);
			sql = "delete from cat2_t";
			statement.execute(sql);
			sql = "delete from cat1_t";
			statement.execute(sql);
		}
		finally
		{
			DBUtils.close(null, statement, null);
		}
	}
	
	public List<String> getCustomWords(long userId, Connection conn) throws NamingException, SQLException
	{
		PreparedStatement statement = null;
		ResultSet rs = null;
		try
		{
			List<String> words = new ArrayList<String>();
			String sql = "select word_c from custom_words_t where user_id_c=?";
			statement = conn.prepareStatement(sql);
			statement.setLong(1, userId);
			rs = statement.executeQuery();
			while (rs.next())
			{
				words.add(rs.getString("word_c"));
			}
			return words;
		}
		finally
		{
			DBUtils.close(null, statement, rs);
		}
	}
	
	public void addCustomWords(String word, long userId, Connection conn) throws NamingException, SQLException
	{
		PreparedStatement statement = null;
		try
		{
			String sql = "insert into custom_words_t(word_c, user_id_c) values(?,?)";
			statement = conn.prepareStatement(sql);
			statement.setString(1, word);
			statement.setLong(2, userId);
			statement.executeUpdate();
		}
		finally
		{
			DBUtils.close(null, statement, null);
		}
	}
	
	public void deleteCustomWords(String word, long userId, Connection conn) throws NamingException, SQLException
	{
		PreparedStatement statement = null;
		try
		{
			String sql = "delete from custom_words_t where user_id_c=? and word_c=?";
			statement = conn.prepareStatement(sql);
			statement.setLong(1, userId);
			statement.setString(2, word);
			statement.executeUpdate();
		}
		finally
		{
			DBUtils.close(null, statement, null);
		}
	}
	
	public List<TitleHistoryItem> getTitleHistory(long numIid, Connection conn) throws NamingException, SQLException
	{
		PreparedStatement statement = null;
		ResultSet rs = null;
		try
		{
			List<TitleHistoryItem> items = new ArrayList<TitleHistoryItem>();
			String sql = "select * from title_history_t where num_iid_c=? order by time_c desc";
			statement = conn.prepareStatement(sql);
			statement.setLong(1, numIid);
			rs = statement.executeQuery();
			while (rs.next())
			{
				TitleHistoryItem item = new TitleHistoryItem();
				item.setNumIid(numIid);
				item.setTitle(rs.getString("title_c"));
				item.setTime(rs.getTimestamp("time_c"));
				items.add(item);
			}
			return items;
		}
		finally
		{
			DBUtils.close(null, statement, rs);
		}
	}
	
	public void addTitleHistoryItem(String title, long numIid, Connection conn) throws NamingException, SQLException
	{
		Timestamp time = new Timestamp(System.currentTimeMillis());
		PreparedStatement statement = null;
		try
		{
			String sql = "insert into title_history_t(num_iid_c,title_c,time_c) values(?,?,?)";
			statement = conn.prepareStatement(sql);
			statement.setLong(1, numIid);
			statement.setString(2, title);
			statement.setTimestamp(3, time);
			statement.executeUpdate();
		}
		finally
		{
			DBUtils.close(null, statement, null);
		}
	}
	
	public void deleteTitleHistory(List<TitleHistoryItem> items, Connection conn) throws NamingException, SQLException
	{
		PreparedStatement statement = null;
		try
		{
			String sql = "delete from title_history_t where num_iid_c=? and time_c=?";
			statement = conn.prepareStatement(sql);
			for (TitleHistoryItem item : items)
			{
				statement.setLong(1, item.getNumIid());
				statement.setTimestamp(2, new Timestamp(item.getTime().getTime()));
				statement.addBatch();
			}
			statement.executeBatch();
		}
		finally
		{
			DBUtils.close(null, statement, null);
		}
	}
}
